﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {
        public static DataTable Search_GetAllCountries(int LanguageCode)
        {
            try
            {
                var sql = "SELECT CountryId, CountryName from CountryDetails WHERE LanguageCode = @LanguageCode";

                _Command.CommandText = sql;

                _Command.Parameters.Clear();
                _Command.Parameters.Add(new SqlParameter("LanguageCode", LanguageCode));

                _DataTable = new DataTable();

                _Command.CommandType = CommandType.Text;

                _Adapter.Fill(_DataTable);

                _Command.CommandType = CommandType.StoredProcedure;

                return _DataTable;
            }
            catch (Exception exc)
            {
                _Command.CommandType = CommandType.StoredProcedure;
                return new DataTable();
            }

        }

        public static DataTable Search_GetAllContactEntities(int LanguageCode)
        {
            try
            {
                var sql = @"
                            SELECT * FROM ItemMaster INNER JOIN ItemDetails ON dbo.ItemMaster.ItemId = dbo.ItemDetails.ItemId
                            WHERE LanguageCode = @LanguageCode AND CategoryId = @CategoryId AND ParentItemId = @ParentItemId";

                _Command.CommandText = sql;

                _Command.Parameters.Clear();

                _Command.CommandType = CommandType.Text;

                _Command.Parameters.Add(new SqlParameter("LanguageCode", LanguageCode));
                _Command.Parameters.Add(new SqlParameter("CategoryId", CategoriesEnum.Entity));
                _Command.Parameters.Add(new SqlParameter("ParentItemId", 1));

                _DataTable = new DataTable();

                _Adapter.Fill(_DataTable);

                _Command.CommandType = CommandType.StoredProcedure;

                return _DataTable;
            }
            catch (Exception exc)
            {
                _Command.CommandType = CommandType.StoredProcedure;
                return new DataTable();
            }
        }

        public static DataTable Search_GetAllProjectEntities(int LanguageCode)
        {
            try
            {
                var sql = @"
                            SELECT * FROM ItemMaster INNER JOIN ItemDetails ON dbo.ItemMaster.ItemId = dbo.ItemDetails.ItemId
                            WHERE LanguageCode = @LanguageCode AND CategoryId = @CategoryId AND ParentItemId = @ParentItemId";

                _Command.CommandText = sql;

                _Command.Parameters.Clear();

                _Command.CommandType = CommandType.Text;

                _Command.Parameters.Add(new SqlParameter("LanguageCode", LanguageCode));
                _Command.Parameters.Add(new SqlParameter("CategoryId", CategoriesEnum.Entity));
                _Command.Parameters.Add(new SqlParameter("ParentItemId", 2));

                _DataTable = new DataTable();

                _Adapter.Fill(_DataTable);

                _Command.CommandType = CommandType.StoredProcedure;

                return _DataTable;
            }
            catch (Exception exc)
            {
                _Command.CommandType = CommandType.StoredProcedure;
                return new DataTable();
            }
        }

        public static List<TinyItem> Search_GetAvailableContacts(int CountryId, int EntityId, int LanguageCode)
        {
            _Command.CommandText = "SP_GetAvailableParentContacts";
            _Command.CommandType = CommandType.StoredProcedure;

            _DataSet = new DataSet();

            _Command.Parameters.Clear();

            // Current ContactId Parameter
            _Command.Parameters.Add("@CurrenContactId", SqlDbType.Int).Value = 0;

            // CountryId Parameter
            if (CountryId != 0)
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;
            else
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = DBNull.Value;


            // EntityId Parameter
            if (EntityId != 0)
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;
            else
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = DBNull.Value;

            // Language Code Parameter
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            _Adapter.SelectCommand = _Command;

            try
            {

                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                List<TinyItem> lstAvailableParentConacts = new List<TinyItem>();

                foreach (DataRow dr in _DataSet.Tables[0].Rows)
                {
                    TinyItem tempItem = new TinyItem();
                    tempItem.ID = Convert.ToInt32(dr["ParentContactId"]);
                    tempItem.Name = Convert.ToString(dr["ParentContactName"]);

                    lstAvailableParentConacts.Add(tempItem);
                }
                TinyItem emptyItem = new TinyItem();
                emptyItem.ID = 0;
                emptyItem.Name = "";
                lstAvailableParentConacts.Insert(0, emptyItem);
                return lstAvailableParentConacts;

            }
            else
                return new List<TinyItem>();


        }

        public static List<TinyItem> Search_GetListItemsLookUps(int CategoryId, int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetListItemsLookUps";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception)
            { return null; }

        }

        public static List<TinyItem> Search_GetAvailableProjects(int CountryId, int ProjectGroupId, int ProjectTypeId, int LanguageCode)
        {
            _Command.CommandText = "SP_GetAvailableProjectsByGroupAndType";
            _Command.CommandType = CommandType.StoredProcedure;

            _DataSet = new DataSet();

            _Command.Parameters.Clear();


            // CountryId Parameter
            if (CountryId != 0)
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;
            else
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = DBNull.Value;

            // ProjectGroupId Parameter
            if (ProjectGroupId != 0)
                _Command.Parameters.Add("@ProjectGroupId", SqlDbType.Int).Value = ProjectGroupId;
            else
                _Command.Parameters.Add("@ProjectGroupId", SqlDbType.Int).Value = DBNull.Value;


            // ProjectTypeId Parameter
            if (ProjectTypeId != 0)
                _Command.Parameters.Add("@ProjectTypeId", SqlDbType.Int).Value = ProjectTypeId;
            else
                _Command.Parameters.Add("@ProjectTypeId", SqlDbType.Int).Value = DBNull.Value;

            // Language Code Parameter
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                CloseConnection();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                List<TinyItem> lstAvailableProjects = new List<TinyItem>();

                foreach (DataRow dr in _DataSet.Tables[0].Rows)
                {
                    TinyItem tempItem = new TinyItem();
                    tempItem.ID = Convert.ToInt32(dr["ProjectId"]);
                    tempItem.Name = Convert.ToString(dr["ProjectName"]);

                    lstAvailableProjects.Add(tempItem);
                }
                TinyItem emptyItem = new TinyItem();
                emptyItem.ID = 0;
                emptyItem.Name = "";
                lstAvailableProjects.Insert(0, emptyItem);
                return lstAvailableProjects;

            }
            else
                return new List<TinyItem>();
        }

        public static string GetRecordLink(string Type, int RecordId)
        {
            _Command.CommandText = "SP_GetRecordURLByRecordId";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@RecordType", SqlDbType.NVarChar).Value = Type;
            _Command.Parameters.Add("@RecordId", SqlDbType.Int).Value = RecordId;

            _Adapter.SelectCommand = _Command;
            _DataSet.Tables.Clear();

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception)
            {
                CloseConnection();
                return "";
            }
            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return Convert.ToString(_DataSet.Tables[0].Rows[0][0]);
            else
                return "";

        }

        public static DataTable SearchContacts(int CountryId, int EntityId, int LanguageCode)
        {
            _Command.CommandText = "SP_SearchContacts";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            if (CountryId == 0)
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            if (EntityId == 0)
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;

            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception)
            {
                CloseConnection();
                return null;
            }
            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;
        }

        public static DataTable SearchProjects(int CountryId, int ProjectGroupId, int ProjectTypeId, int LanguageCode)
        {
            _Command.CommandText = "SP_SearchProjects";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            if (CountryId == 0)
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            if (ProjectGroupId == 0)
                _Command.Parameters.Add("@ProjectGroupId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@ProjectGroupId", SqlDbType.Int).Value = ProjectGroupId;

            if (ProjectTypeId == 0)
                _Command.Parameters.Add("@ProjectTypeId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@ProjectTypeId", SqlDbType.Int).Value = ProjectTypeId;

            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception)
            {
                CloseConnection();
                return null;
            }
            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;
        }

        public static DataTable SearchItemsAndCadres(int LanguageCode, int CountryId, string keyWord)
        {
            _Command.CommandText = "SP_SearchItemValuesCadres";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            if (CountryId == 0)
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = DBNull.Value;
            else
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;

            _Command.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyWord;

            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception)
            {
                CloseConnection();
                return null;
            }
            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;
        }

        public static List<TinyItem> Search_GetListItemsLookUpsByParent(int CategoryId, int ParentId, int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "SP_GetItemLookUpsByParentId";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                if (ParentId == 0)
                    _Adapter.SelectCommand.Parameters.Add("@ParentId", SqlDbType.Int).Value = DBNull.Value;
                else
                    _Adapter.SelectCommand.Parameters.Add("@ParentId", SqlDbType.Int).Value = ParentId;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception)
            { return null; }

        }
    }
}
